package edu.unika.aifb.graphindex.importer.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;
import org.openrdf.model.vocabulary.RDF;

import edu.unika.aifb.graphindex.importer.Importer;
import edu.unika.aifb.graphindex.importer.ImporterRegistry;
import edu.unika.aifb.graphindex.importer.db.metadata.Column;
import edu.unika.aifb.graphindex.importer.db.metadata.Database;
import edu.unika.aifb.graphindex.importer.db.metadata.ForeignKeyConstraint;
import edu.unika.aifb.graphindex.importer.db.metadata.Table;

public class DBImporter extends Importer{
	private ConnectionPool connectionPool;
	private Database database;
	private String m_dbimporter_ns = "http://fzi.de/dbimporter#";
	private String m_dbimporter_fk = "http://fzi.de/dbimporter/fk#";
	private String m_rdf_type = RDF.TYPE.toString();
	
	private final static Logger log = Logger.getLogger(DBImporter.class);
	
	static {
		ImporterRegistry.getInstance().registerImporter("db", DBImporter.class, Arrays.asList(""));
	}
	
	public DBImporter(){
		super();
	}

	@Override
	public void doImport(){
		try{
			for(Table table : database.getTables()){
				Collection<Column> columns = new HashSet<Column>(table.getColumns());
				Set<Column> primaryKeyColumns = table.getPrimaryKeyColumns();
				
				if(primaryKeyColumns.isEmpty())
					continue;
				
				Set<Column> foreignKeyColumns = table.getForeignKeyColumns();
				columns.removeAll(foreignKeyColumns);
				
				String sql = createSelectStatement(table);
				ResultSet rs = connectionPool.executeQuery(sql);
				
				if(rs == null){
					log.debug("Query " + sql + " cannot be executed on any database");
					continue;
				}
				log.debug(table.getName());
				log.debug(foreignKeyColumns);
				log.debug(columns);
				log.debug(sql);
				while (rs.next()) {
					String tableName = table.getName();
					String tableNameId = String.format("%s%s", m_dbimporter_ns, tableName);
					
					String tupleId = String.format("%s%s", m_dbimporter_ns, table.getName());
					
					for(Column pkColumn : primaryKeyColumns){
						String pkValue = rs.getString(pkColumn.getName()).replace(" ", "_");
						tupleId += String.format(".%s", pkValue);
					}
					
					this.m_sink.triple(tupleId, m_rdf_type, tableNameId, null);
					
					
					for(Column column : columns){
						String columnNameId = String.format("%s.%s", tableNameId, column.getName());
						String value = rs.getString(column.getName());
						if(value == null)
							value = "NULL";
						this.m_sink.triple(tupleId, columnNameId, value, null);
					}

					Set<String>  foreignKeyNames = table.getForeignKeyNames();
					for(String foreignKeyName : foreignKeyNames){
						ForeignKeyConstraint foreignKeyConstraint = table.getForeignKeyConstraint(foreignKeyName);
						List<Column> sourceColumns = foreignKeyConstraint.getSourceColumns();
						List<Column> targetColumns = foreignKeyConstraint.getTargetColumns();
						Table targetTable = targetColumns.get(0).getTable();
						String targetTupleId = String.format("%s%s", m_dbimporter_ns, targetTable.getName());
						
						String foreignKeyNameWithNamespace = String.format("%s%s", m_dbimporter_fk, foreignKeyName);
						
						for(Column targetColumn : targetTable.getPrimaryKeyColumns()){
							Column sourceColumn = sourceColumns.get(targetColumns.indexOf(targetColumn));
							String sourceColumnValue = rs.getString(sourceColumn.getName());
							if(sourceColumnValue == null || sourceColumnValue.trim().equalsIgnoreCase("null") || sourceColumnValue.trim() == ""){
								targetTupleId = null;
								break;
							}
							targetTupleId += String.format(".%s", sourceColumnValue.replace(" ", "_"));
						}
						
						
						if(targetTupleId == null)
							continue;
						
						this.m_sink.triple(tupleId, foreignKeyNameWithNamespace, targetTupleId, null);
					}
				}
			}
			
			connectionPool.closeAllConnection();
			
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	
	
	public void setConnectionPool(ConnectionPool connectionPool) {
		this.connectionPool = connectionPool;
	}

	public void setDatabase(Database database) {
		this.database = database;
	}

	private String createSelectStatement(Table table){
		StringBuffer selectStatement = new StringBuffer();
		selectStatement.append("SELECT ");
		for(Column column : table.getColumns()){
			selectStatement.append(column.getName()).append(", ");
		}
		selectStatement.delete(selectStatement.length() - 2, selectStatement.length());
		selectStatement.append(" FROM ");
		if(table.getSchema() != null && table.getSchema().trim().length() > 0)
			selectStatement.append(table.getSchema()).append(".");
		selectStatement.append(table.getName());
		
		return selectStatement.toString();
	}
}
